﻿GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_Users](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](100) NOT NULL,
	[Password] [nvarchar](255) NOT NULL,
	[LastLoginTime] [datetime] NULL,
	[EmailAddress] [nvarchar](100) NULL,
 CONSTRAINT [PK_be_Users] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[be_Roles]    Script Date: 07/30/2008 21:56:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_Roles](
	[RoleID] [int] IDENTITY(1,1) NOT NULL,
	[Role] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_be_Roles] PRIMARY KEY CLUSTERED 
(
	[RoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[be_UserRoles]    Script Date: 07/31/2008 12:26:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_UserRoles](
	[UserRoleID] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NOT NULL,
	[RoleID] [int] NOT NULL,
 CONSTRAINT [PK_be_UserRoles] PRIMARY KEY CLUSTERED 
(
	[UserRoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[be_UserRoles]  WITH CHECK ADD  CONSTRAINT [FK_be_UserRoles_be_Roles] FOREIGN KEY([RoleID])
REFERENCES [dbo].[be_Roles] ([RoleID])
GO
ALTER TABLE [dbo].[be_UserRoles] CHECK CONSTRAINT [FK_be_UserRoles_be_Roles]
GO
ALTER TABLE [dbo].[be_UserRoles]  WITH CHECK ADD  CONSTRAINT [FK_be_UserRoles_be_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[be_Users] ([UserID])
GO
ALTER TABLE [dbo].[be_UserRoles] CHECK CONSTRAINT [FK_be_UserRoles_be_Users]
GO

/* Setting up admin access to all blogs (BlogAdministrator) and to the site main blog (SiteBlogEditor).   */
/* BEGIN */

/* Create initial Admin and Testguy users ------------------------- */

INSERT INTO be_Users (UserName, Password, LastLoginTime, EmailAddress)
	VALUES ('Admin', '', GETDATE(), 'admin@yourdomain.com');
INSERT INTO be_Users (UserName, Password, LastLoginTime, EmailAddress) 
	VALUES ('Testguy', '', GETDATE(), 'testguy@yourdomain.com');	
	
/* Create roles to support Admin and Testguy at Blog Admin and Editor respectively of Sueetie Site Blog ------------------------- */
	
INSERT INTO be_Roles (Role) VALUES ('BlogAdministrator');
INSERT INTO be_Roles (Role) VALUES ('SiteBlogEditor');  

/* Create user-to-role relationship.  Admin - BlogAdministrator, Testguy - SiteBlogEditor ------------------------- */

INSERT INTO be_UserRoles (UserID, RoleID) VALUES (1, 1);
INSERT INTO be_UserRoles (UserID, RoleID) VALUES (2, 2);

/* END */

/* This is an example of adding a user as an editor of a blog.  Each blog has an editor role, here "SecondBlogEditor" */

/* BEGIN */


--/* Obtain the values of userID and RoleID for "testguy" in your be_users and be_roles table respectively */

--INSERT INTO be_Roles (Role) VALUES ('SecondBlogEditor');

--select * from be_users
--select * from be_roles
--select * from be_userRoles

--INSERT INTO be_Users (UserName, Password, LastLoginTime, EmailAddress)
--	VALUES ('SecondTestguy', '', GETDATE(), 'secondtestguy@yourdomain.com');
--INSERT INTO be_UserRoles (UserID, RoleID)
--VALUES (3, 3);

/* END */
